﻿-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/07/2014 10:32:19
-- Generated from EDMX file: D:\WorkspaceNet\GDSignature\StampController.DAL\GDSignatureModel.edmx
-- --------------------------------------------------

DROP DATABASE [gdsignaturedb];
GO
SET QUOTED_IDENTIFIER OFF;
GO
USE master;
GO
CREATE DATABASE [gdsignaturedb];
GO
USE [gdsignaturedb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ApprovalHistory_RequestForm]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ApprovalHistory] DROP CONSTRAINT [FK_ApprovalHistory_RequestForm];
GO
IF OBJECT_ID(N'[dbo].[FK_Users_Department]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_Users_Department];
GO
IF OBJECT_ID(N'[dbo].[FK_Document_RequestForm]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Document] DROP CONSTRAINT [FK_Document_RequestForm];
GO
IF OBJECT_ID(N'[dbo].[FK_RequestForm_RequiredType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RequestForm] DROP CONSTRAINT [FK_RequestForm_RequiredType];
GO
IF OBJECT_ID(N'[dbo].[FK_RequestForm_Stamp]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RequestForm] DROP CONSTRAINT [FK_RequestForm_Stamp];
GO
IF OBJECT_ID(N'[dbo].[FK_RequestForm_StampType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RequestForm] DROP CONSTRAINT [FK_RequestForm_StampType];
GO
IF OBJECT_ID(N'[dbo].[FK_RequestForm_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RequestForm] DROP CONSTRAINT [FK_RequestForm_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Users_Site]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_Users_Site];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[ApprovalHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ApprovalHistory];
GO
IF OBJECT_ID(N'[dbo].[Department]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Department];
GO
IF OBJECT_ID(N'[dbo].[Document]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Document];
GO
IF OBJECT_ID(N'[dbo].[RequestForm]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RequestForm];
GO
IF OBJECT_ID(N'[dbo].[RequiredType]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RequiredType];
GO
IF OBJECT_ID(N'[dbo].[Site]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Site];
GO
IF OBJECT_ID(N'[dbo].[Stamp]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Stamp];
GO
IF OBJECT_ID(N'[dbo].[StampType]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StampType];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'ApprovalHistory'
CREATE TABLE [dbo].[ApprovalHistory] (
    [ID] uniqueidentifier  NOT NULL,
    [RequestId] int  NOT NULL,
    [ApprovedBy] nvarchar(50)  NOT NULL,
    [ApprovedDate] datetime  NOT NULL,
    [DeptID] int  NOT NULL,
    [Comment] nvarchar(max)  NULL,
    [Active] bit  NOT NULL DEFAULT 1,
    [Status] int  NOT NULL
);
GO


CREATE TABLE [dbo].[Category](
	[CategoryId] [int] NOT NULL,
	[CategoryName] [nvarchar](500) NOT NULL
);
GO

-- Creating table 'Department'
CREATE TABLE [dbo].[Department] (
    [DeptID] int  NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [Remark] nvarchar(max)  NULL
);
GO

-- Creating table 'Document'
CREATE TABLE [dbo].[Document] (
    [DocID] int IDENTITY(1,1) NOT NULL,
    [Link] varchar(300)  NULL,
    [OriginName] nvarchar(300)  NULL,
    [PagesNo] int  NOT NULL,
    [CopiesNo] int  NULL,
    [RequestID] int  NOT NULL
);
GO

-- Creating table 'RequestForm'
CREATE TABLE [dbo].[RequestForm] (
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryId] [int] NULL,
	[RequestID] [varchar](20) NULL,
	[UserID] [varchar](50) NOT NULL,
	[RequestFor] [varchar](50) NULL,
	[DateRequest] [datetime] NOT NULL,
	[DateNeeded] [datetime] NULL,
	[Purpose] [nvarchar](300) NULL,
	[SignByID] [int] NOT NULL,
	[RequiredBy] [int] NOT NULL,
	[StampID] [int] NOT NULL,
	[STID] [int] NOT NULL,
	[RequestType] [int] NOT NULL,
	[DefineName] [nvarchar](200) NULL,
	[Remark] [ntext] NULL,
	[Deleted] [bit] NOT NULL,
	[LastApprovedBy] [varchar](50) NULL,
	[LastApprovedDate] [datetime] NULL,
	[IsCompleted] [bit] NOT NULL,
	[AssignedTo] [int] NULL
);
GO

-- Creating table 'RequiredType'
CREATE TABLE [dbo].[RequiredType] (
    [RequiredTypeID] int  NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [Remark] nvarchar(max)  NULL
);
GO

-- Creating table 'Site'
CREATE TABLE [dbo].[Site] (
    [SiteID] int  NOT NULL,
    [Name] nvarchar(300)  NOT NULL,
    [Address] nvarchar(500)  NULL,
    [Phone] varchar(20)  NULL
);
GO

-- Creating table 'Stamp'
CREATE TABLE [dbo].[Stamp] (
    [StampID] int NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [Remark] nvarchar(max)  NULL
);
GO

-- Creating table 'StampType'
CREATE TABLE [dbo].[StampType] (
    [STID] int  NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [Remark] nvarchar(max)  NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserID] varchar(50)  NOT NULL,
    [Name] nvarchar(100)  NULL,
    [SiteID] int  NOT NULL,
    [DeptID] int  NULL,
    [Email] nvarchar(200)  NULL,
    [IsAdminSite] bit  NOT NULL DEFAULT 0,
    [IsAdminOfficer] bit  NOT NULL DEFAULT 0,
    [IsHOD] bit  NOT NULL DEFAULT 0,
    [IsGD] bit  NOT NULL DEFAULT 0
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'ApprovalHistory'
ALTER TABLE [dbo].[ApprovalHistory]
ADD CONSTRAINT [PK_ApprovalHistory]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ApprovalHistory'
ALTER TABLE [dbo].[Category]
ADD CONSTRAINT [PK_Category]
    PRIMARY KEY CLUSTERED ([CategoryId] ASC);
GO

-- Creating primary key on [DeptID] in table 'Department'
ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [PK_Department]
    PRIMARY KEY CLUSTERED ([DeptID] ASC);
GO

-- Creating primary key on [DocID] in table 'Document'
ALTER TABLE [dbo].[Document]
ADD CONSTRAINT [PK_Document]
    PRIMARY KEY CLUSTERED ([DocID] ASC);
GO

-- Creating primary key on [ID] in table 'RequestForm'
ALTER TABLE [dbo].[RequestForm]
ADD CONSTRAINT [PK_RequestForm]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [RequiredTypeID] in table 'RequiredType'
ALTER TABLE [dbo].[RequiredType]
ADD CONSTRAINT [PK_RequiredType]
    PRIMARY KEY CLUSTERED ([RequiredTypeID] ASC);
GO

-- Creating primary key on [SiteID] in table 'Site'
ALTER TABLE [dbo].[Site]
ADD CONSTRAINT [PK_Site]
    PRIMARY KEY CLUSTERED ([SiteID] ASC);
GO

-- Creating primary key on [StampID] in table 'Stamp'
ALTER TABLE [dbo].[Stamp]
ADD CONSTRAINT [PK_Stamp]
    PRIMARY KEY CLUSTERED ([StampID] ASC);
GO

-- Creating primary key on [STID] in table 'StampType'
ALTER TABLE [dbo].[StampType]
ADD CONSTRAINT [PK_StampType]
    PRIMARY KEY CLUSTERED ([STID] ASC);
GO

-- Creating primary key on [UserID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [RequestId] in table 'ApprovalHistory'
ALTER TABLE [dbo].[ApprovalHistory]
ADD CONSTRAINT [FK_ApprovalHistory_RequestForm]
    FOREIGN KEY ([RequestId])
    REFERENCES [dbo].[RequestForm]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ApprovalHistory_RequestForm'
CREATE INDEX [IX_FK_ApprovalHistory_RequestForm]
ON [dbo].[ApprovalHistory]
    ([RequestId]);
GO

-- Creating foreign key on [DeptID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_Users_Department]
    FOREIGN KEY ([DeptID])
    REFERENCES [dbo].[Department]
        ([DeptID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Users_Department'
CREATE INDEX [IX_FK_Users_Department]
ON [dbo].[Users]
    ([DeptID]);
GO

-- Creating foreign key on [RequestID] in table 'Document'
ALTER TABLE [dbo].[Document]
ADD CONSTRAINT [FK_Document_RequestForm]
    FOREIGN KEY ([RequestID])
    REFERENCES [dbo].[RequestForm]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Document_RequestForm'
CREATE INDEX [IX_FK_Document_RequestForm]
ON [dbo].[Document]
    ([RequestID]);
GO

-- Creating foreign key on [RequiredBy] in table 'RequestForm'
ALTER TABLE [dbo].[RequestForm]
ADD CONSTRAINT [FK_RequestForm_RequiredType]
    FOREIGN KEY ([RequiredBy])
    REFERENCES [dbo].[RequiredType]
        ([RequiredTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RequestForm_RequiredType'
CREATE INDEX [IX_FK_RequestForm_RequiredType]
ON [dbo].[RequestForm]
    ([RequiredBy]);
GO

-- Creating foreign key on [StampID] in table 'RequestForm'
ALTER TABLE [dbo].[RequestForm]
ADD CONSTRAINT [FK_RequestForm_Stamp]
    FOREIGN KEY ([StampID])
    REFERENCES [dbo].[Stamp]
        ([StampID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RequestForm_Stamp'
CREATE INDEX [IX_FK_RequestForm_Stamp]
ON [dbo].[RequestForm]
    ([StampID]);
GO

-- Creating foreign key on [STID] in table 'RequestForm'
ALTER TABLE [dbo].[RequestForm]
ADD CONSTRAINT [FK_RequestForm_StampType]
    FOREIGN KEY ([STID])
    REFERENCES [dbo].[StampType]
        ([STID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RequestForm_StampType'
CREATE INDEX [IX_FK_RequestForm_StampType]
ON [dbo].[RequestForm]
    ([STID]);
GO

-- Creating foreign key on [UserID] in table 'RequestForm'
ALTER TABLE [dbo].[RequestForm]
ADD CONSTRAINT [FK_RequestForm_Users]
    FOREIGN KEY ([UserID])
    REFERENCES [dbo].[Users]
        ([UserID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RequestForm_Users'
CREATE INDEX [IX_FK_RequestForm_Users]
ON [dbo].[RequestForm]
    ([UserID]);
GO

-- Creating foreign key on [SiteID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_Users_Site]
    FOREIGN KEY ([SiteID])
    REFERENCES [dbo].[Site]
        ([SiteID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Users_Site'
CREATE INDEX [IX_FK_Users_Site]
ON [dbo].[Users]
    ([SiteID]);
GO

INSERT [dbo].[Department] ([DeptID], [Name], [Remark]) VALUES (1, N'GD', 'General Director')
GO
INSERT [dbo].[Department] ([DeptID], [Name], [Remark]) VALUES (2, N'IT', 'Information Technology')
GO
INSERT [dbo].[Department] ([DeptID], [Name], [Remark]) VALUES (3, N'HR', 'Human Resource')
GO
INSERT [dbo].[Department] ([DeptID], [Name], [Remark]) VALUES (4, N'AC', 'Accountant')
GO
INSERT [dbo].[RequiredType] ([RequiredTypeID], [Name], [Remark]) VALUES (1, N'Authority', NULL)
GO
INSERT [dbo].[RequiredType] ([RequiredTypeID], [Name], [Remark]) VALUES (2, N'Customer', NULL)
GO
INSERT [dbo].[RequiredType] ([RequiredTypeID], [Name], [Remark]) VALUES (3, N'Supplier', NULL)
GO
INSERT [dbo].[RequiredType] ([RequiredTypeID], [Name], [Remark]) VALUES (4, N'Internal', NULL)
GO
INSERT [dbo].[Site] ([SiteID], [Name], [Address], [Phone]) VALUES (1, N'airliquide', N'Dist.9', NULL)
GO
INSERT [dbo].[StampType] ([STID], [Name], [Remark]) VALUES (1, N'Hanging Stamp', NULL)
GO
INSERT [dbo].[StampType] ([STID], [Name], [Remark]) VALUES (2, N'Stamp on signature', NULL)
GO
INSERT [dbo].[StampType] ([STID], [Name], [Remark]) VALUES (3, N'Linking Page Stamp', NULL)
GO
INSERT [dbo].[Stamp] ([StampID], [Name], [Remark]) VALUES (1, N'Company Stamp', NULL)
GO
INSERT [dbo].[Stamp] ([StampID], [Name], [Remark]) VALUES (2, N'Branch Company Stamp', NULL)
GO
INSERT [dbo].[Users] ([UserID], [Name], [SiteID], [DeptID], [Email], [IsAdminSite], [IsAdminOfficer], [IsHOD], [IsGD]) 
VALUES (N'HUUTAI\Tai', N'Liêu Hữu Tài', 1, 2, N'huutai.alz@gmail.com', 0, 0, 0, 0)
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------